﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QL_DTTN.DAL;
namespace QL_DTTN.DTO.DTO
{
    public partial class ThiSinh
    {
        public bool Them(ThiSinh thiSinh)
        {
            string str = " insert into " + _tableName + @" (SoBaoDanh,HoTen,DiaChi,GioiTinh,SoCMND,NgaySinh,NgayVaoDang,NgayVaoDoan,MaDanToc,MaDoiTuong,MaHeDaoTao,MaKyThi,MaTinh,MaTruong)
                                                   values (@pSoBaoDanh,@pHoTen,@pDiaChi,@pGioiTinh,@pSoCMND,@pNgaySinh,@pNgayVaoDang,@pNgayVaoDoan,@pMaDanToc,@pMaDoiTuong,@pMaHeDaoTao,@pMaKyThi,@pMaTinh,@pMaTruong)  ";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pSoBaoDanh", thiSinh.SoBaoDanh));
            dp.AddParameter(new SqlParameter("@pHoTen", thiSinh.HoTen));
            dp.AddParameter(new SqlParameter("@pDiaChi", thiSinh.DiaChi));
            dp.AddParameter(new SqlParameter("@pGioiTinh", thiSinh.GioiTinh));
            dp.AddParameter(new SqlParameter("@pSoCMND", thiSinh.SoCMND));
            dp.AddParameter(new SqlParameter("@pNgaySinh", thiSinh.NgaySinh));
            dp.AddParameter(new SqlParameter("@pNgayVaoDang", ((thiSinh.NgayVaoDang != new DateTime()) ? thiSinh.NgayVaoDang : Convert.DBNull)));
            dp.AddParameter(new SqlParameter("@pNgayVaoDoan", ((thiSinh.NgayVaoDoan != new DateTime()) ? thiSinh.NgayVaoDoan : Convert.DBNull)));
            dp.AddParameter(new SqlParameter("@pMaDanToc", thiSinh.MaDanToc));
            dp.AddParameter(new SqlParameter("@pMaDoiTuong", thiSinh.MaDoiTuong));
            dp.AddParameter(new SqlParameter("@pMaHeDaoTao", thiSinh.MaHeDaoTao));
            dp.AddParameter(new SqlParameter("@pMaKyThi", thiSinh.MaKyThi));
            dp.AddParameter(new SqlParameter("@pMaTinh", thiSinh.MaTinh));
            dp.AddParameter(new SqlParameter("@pMaTruong", thiSinh.MaTruong));
            return dp.RunSQL(str, true);
        }
        public bool Them(List<ThiSinh> lstThiSinh)
        {
            SqlDataProvider dp = new SqlDataProvider();

            List<string> lstStr = new List<string>();
            int i = 1;
            foreach (var thiSinh in lstThiSinh)
            {
                string str = " insert into " + _tableName + @" (SoBaoDanh,HoTen,DiaChi,GioiTinh,SoCMND,NgaySinh,NgayVaoDang,NgayVaoDoan,MaDanToc,MaDoiTuong,MaHeDaoTao,MaKyThi,MaTinh,MaTruong)
                                                   values (@pSoBaoDanh" + i + ",@pHoTen" + i + ",@pDiaChi" + i + ",@pGioiTinh" + i + ",@pSoCMND" + i + ",@pNgaySinh" + i + ",@pNgayVaoDang" + i + ",@pNgayVaoDoan" + i + ",@pMaDanToc" + i + ",@pMaDoiTuong" + i + ",@pMaHeDaoTao" + i + ",@pMaKyThi" + i + ",@pMaTinh" + i + ",@pMaTruong" + i + ")  ";


                dp.AddParameter(new SqlParameter("@pSoBaoDanh"+i, thiSinh.SoBaoDanh));
                dp.AddParameter(new SqlParameter("@pHoTen" + i, thiSinh.HoTen));
                dp.AddParameter(new SqlParameter("@pDiaChi" + i, thiSinh.DiaChi));
                dp.AddParameter(new SqlParameter("@pGioiTinh" + i, thiSinh.GioiTinh));
                dp.AddParameter(new SqlParameter("@pSoCMND" + i, thiSinh.SoCMND));
                dp.AddParameter(new SqlParameter("@pNgaySinh" + i, thiSinh.NgaySinh));
                dp.AddParameter(new SqlParameter("@pNgayVaoDang" + i, ((thiSinh.NgayVaoDang != new DateTime()) ? thiSinh.NgayVaoDang : Convert.DBNull)));
                dp.AddParameter(new SqlParameter("@pNgayVaoDoan" + i, ((thiSinh.NgayVaoDoan != new DateTime()) ? thiSinh.NgayVaoDoan : Convert.DBNull)));
                dp.AddParameter(new SqlParameter("@pMaDanToc" + i, thiSinh.MaDanToc));
                dp.AddParameter(new SqlParameter("@pMaDoiTuong" + i, thiSinh.MaDoiTuong));
                dp.AddParameter(new SqlParameter("@pMaHeDaoTao" + i, thiSinh.MaHeDaoTao));
                dp.AddParameter(new SqlParameter("@pMaKyThi" + i, thiSinh.MaKyThi));
                dp.AddParameter(new SqlParameter("@pMaTinh" + i, thiSinh.MaTinh));
                dp.AddParameter(new SqlParameter("@pMaTruong" + i, thiSinh.MaTruong));
                i++;
                lstStr.Add(str);
            }
            return dp.runSQLListQuery(lstStr, true);
        }
        public long TongSo(long maKyThi)
        {
            string sql = " select COUNT(SoBaoDanh) from " + _tableName+" where MaKyThi=@pMaKyThi";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pMaKyThi", maKyThi));
            object kq = 0;
            dp.RunReturnSQL(sql, ref kq, true);
            return Convert.ToInt64(kq);
        }
        public int getMaxID(string tiento)
        {
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pSoBaoDanh", tiento + "%"));
            string sql = string.Format(" select Max({1}) from {0} where {1} like @pSoBaoDanh ", _tableName, _keyField);
            object id = null;
            if (dp.RunReturnSQL(sql, ref id, true))
            {
                if (id.ToString() == "" || id == null)
                {
                    return 0;
                }
                else
                {
                    string idx = id.ToString().Substring(tiento.Length + 1);

                    int kq = 0;
                    if (int.TryParse(idx, out kq))
                    {
                        return kq;
                    }
                    else return -1;
                }
            }
            else return -1;
        }
        public DataTable getData(string key, Int16 maTinh, string maTruong, Int16 maHeDaoTao, string maDoiTuong, Int64 maKyThi)
        {
            string sql = "select SoBaoDanh,HoTen,(Case when GioiTinh='True' then 'Nam'  else N'Nữ' end) as GioiTinh,NgaySinh,T.DiaChi,T.MaTinh,SoCMND,NgayVaoDoan,NgayVaoDang,MaDanToc,MaDoiTuong,MaHeDaoTao ";
            sql += " from " + _tableName + " T inner join Truong on Truong.MaTruong=T.MaTruong ";
            sql += " where (HoTen Like @pKey or SoBaoDanh Like @pKey) ";
            sql += ((maTruong == "0") ? ((maTinh == 0) ? "" : " and Truong.MaTinh=@pMaTinh ") : " and T.MaTruong = @pMaTruong ");
            sql += ((maHeDaoTao == 0) ? "" : " and T.MaHeDaoTao = @pMaHeDaoTao ");
            sql += ((maDoiTuong == "0") ? "" : " and T.MaDoiTuong = @pMaDoiTuong ");
            sql += ((maKyThi == 0) ? "" : " and T.MaKyThi = @pMaKyThi ");
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pKey", "%" + key + "%"));
            dp.AddParameter(new SqlParameter("@pMaTruong", maTruong));
            dp.AddParameter(new SqlParameter("@pMaTinh", maTinh));
            dp.AddParameter(new SqlParameter("@pMaHeDaoTao", maHeDaoTao));
            dp.AddParameter(new SqlParameter("@pMaDoiTuong", maDoiTuong));
            dp.AddParameter(new SqlParameter("@pMaKyThi", maKyThi));
            return dp.ExDataTable(sql, true);
        }
        public DataTable dtSoBaoDanh(long maKyThi,long maMonThi,short maTinh)
        {
            string sql = "select SoBaoDanh ";
            sql += " from " + _tableName + " T inner join ChiTietMonThi C on C.MaHeDaoTao=T.MaHeDaoTao inner join Truong on T.MaTruong=Truong.MaTruong ";
            sql += " where T.MaKyThi=@pMaKyThi and C.MaMonThi=@pMaMonThi and Truong.MaTinh=@pMaTinh";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pMaKyThi", maKyThi));
            dp.AddParameter(new SqlParameter("@pMaMonThi", maMonThi));
            dp.AddParameter(new SqlParameter("@pMaTinh", maTinh));
            return dp.ExDataTable(sql, true);
        }
        public int  SoThiSinh(long maKyThi, long maMonThi, short maTinh)
        {
            string sql = "select Count(SoBaoDanh) ";
            sql += " from " + _tableName + " T inner join ChiTietMonThi C on C.MaHeDaoTao=T.MaHeDaoTao inner join Truong on T.MaTruong=Truong.MaTruong ";
            sql += " where T.MaKyThi=@pMaKyThi and C.MaMonThi=@pMaMonThi and Truong.MaTinh=@pMaTinh";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pMaKyThi", maKyThi));
            dp.AddParameter(new SqlParameter("@pMaMonThi", maMonThi));
            dp.AddParameter(new SqlParameter("@pMaTinh", maTinh));
            object kq = 0;
            dp.RunReturnSQL(sql, ref kq, true);
            return Convert.ToInt32(kq);
        }
        public DataTable DataDiemThi(string key, short maTinh, string maTruong, short maHeDaoTao, long maKyThi, long maMonThi, short maTinhD, long maDiemThi, long maPhongThi)
        {
            string sql = "select  T.SoBaoDanh,HoTen,M2.MaMon,M2.TenMon,isNUll(K.KetQua,0) as KetQua  ";
            sql += " from " + _tableName + " T left join ChiTietMonThi C on C.MaHeDaoTao=T.MaHeDaoTao ";
            sql += " inner join MonThi M on M.MaMonThi=C.MaMonThi and M.maKyThi=T.maKyThi ";
            sql += " left join Mon M2 on M2.MaMon=M.MaMon ";
            sql += " left join KetQuaThi K on K.MaMon=M.MaMon and K.SoBaoDanh=T.SoBaoDanh ";
            sql += " left join Truong on Truong.MaTruong=T.MaTruong ";
            sql += " left join LichThi L on T.SoBaoDanh=L.SoBaoDanh and C.MaMonThi=L.MaMonThi ";
            sql += " left join PhongThi P on P.MaPhongThi=L.MaPhongThi ";
            sql += " left join DiemThi D on D.MaDiemThi=P.MaDiemThi ";
            sql += " where (HoTen Like @pKey or T.SoBaoDanh Like @pKey) ";
            sql += ((maTruong == "0") ? ((maTinh == 0) ? "" : " and Truong.MaTinh=@pMaTinh ") : " and T.MaTruong = @pMaTruong ");
            sql += ((maHeDaoTao == 0) ? "" : " and T.MaHeDaoTao = @pMaHeDaoTao ");
            sql += ((maKyThi == 0) ? "" : " and T.MaKyThi = @pMaKyThi ");
            sql += ((maMonThi == 0) ? "" : " and C.MaMonThi = @pMaMonThi ");
            sql += ((maPhongThi == 0) ? ((maDiemThi == 0) ? ((maTinhD == 0) ? "" : " and D.MaTinh=@pMaTinhD ") : " and D.MaDiemThi=@pMaDiemThi ") : " and P.MaPhongThi= @pMaPhongThi ");
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pKey", "%" + key + "%"));
            dp.AddParameter(new SqlParameter("@pMaTruong", maTruong));
            dp.AddParameter(new SqlParameter("@pMaTinh", maTinh));
            dp.AddParameter(new SqlParameter("@pMaHeDaoTao", maHeDaoTao));
            dp.AddParameter(new SqlParameter("@pMaKyThi", maKyThi));
            dp.AddParameter(new SqlParameter("@pMaMonThi", maMonThi));
            dp.AddParameter(new SqlParameter("@pMaTinhD", maTinhD));
            dp.AddParameter(new SqlParameter("@pMaDiemThi", maDiemThi));
            dp.AddParameter(new SqlParameter("@pMaPhongThi", maPhongThi));
            return dp.ExDataTable(sql, true);
        }
    }
}
